<?php
/**
 * Created by PhpStorm.
 * User: andrei
 * Date: Jun 27, 2012
 * Time: 1:46:31 PM
 * To change this template use File | Settings | File Templates.
 */
 
class Db_Products_Products extends Common {
    protected static $tableName = TAB_PRODUCTS;
    protected static $dbFields = array(
        'id',
        'p_size_id',
        'p_type',
        'p_cover_type_id',
        'p_name',
		'p_description',
		'p_long_description',
		'p_group_style',
		'p_default_url',
		'p_is_envelope',
		'p_fonts',
        'deleted'
    );

    /**
     * @var Db_Products_Products
     */
    public $objectArray;
    public $id;
    public $p_size_id;
    public $p_type;
    public $p_cover_type_id;
    public $p_name;
	public $p_description;
	public $p_long_description;
	public $p_group_style;
	public $p_default_url;
	public $p_is_envelope;
	public $p_fonts;
    public $deleted;

    function __construct($value=NULL, $fieldName=NULL) {
        parent::__construct($value, $fieldName);
    }

    /**
     * @static
     * @param $productId
     * @return array|null
     * This function get all themes by one product
     */
    public static function getProductThemes($productId){
		$sql = "SELECT `id`, `te_name`";
		if (!empty($productId) && is_numeric($productId)) {
			$sql .= ", `pt_theme_id`";
		}
		$sql .= " FROM ?f";
		if (!empty($productId) && is_numeric($productId)) {
			$sql .= " LEFT JOIN ?f ON `id`=`pt_theme_id` AND `pt_product_id`=?";
		}
		$sql .= " WHERE deleted=0";
		if (!empty($productId) && is_numeric($productId)) {
			$sql .= " ORDER BY pt_is_default DESC";
		}
		return self::findArray($sql, TAB_THEMES, TAB_PRODUCT_THEME_PHOTOS, $productId);
    }

	public static function rewriteProductThemes($productId, $themes) {
		$defaultThemes = array_fill(0, count($themes), 0);
		$defaultThemes[0] = 1;
		$sql = "DELETE FROM ?f WHERE pt_product_id = ? AND NOT pt_theme_id IN (?)";
		self::$DB->q($sql, TAB_PRODUCT_THEME_PHOTOS, $productId, $themes);
		$data = array();
		foreach($themes as $nr => $themeId) {
			$data[] = array(
				'pt_product_id' => $productId,
				'pt_theme_id' => $themeId,
				'pt_is_default' => (0 == $nr) ? 1 : 0
			);
		}
		self::$DB->insertBatch(TAB_PRODUCT_THEME_PHOTOS, $data, true);
		return true;
	}

	public static function getProductCategories($productId) {
		$sql = "SELECT `id`, `pc_name`";
		if (!empty($productId) && is_numeric($productId)) {
			$sql .= ", `prc_category_id`";
		}
		$sql .= " FROM ?f";
		if (!empty($productId) && is_numeric($productId)) {
			$sql .= " LEFT JOIN ?f ON `id`=`prc_category_id` AND `prc_product_id`=?";
		}
		$sql .= " WHERE deleted=0";
		if (!empty($productId) && is_numeric($productId)) {
			$sql .= " ORDER BY prc_is_default DESC";
		}
		return self::findArray($sql, TAB_CATEGORIES, TAB_PRODUCT_CATEGORIES, $productId);
	}

	public static function rewriteProductCategories($productId, $categories) {
		$sql = "DELETE FROM ?f WHERE prc_product_id = ?";
		if (!empty($categories)) {
			$sql .= " AND NOT prc_category_id IN (?)";
		}
		self::$DB->q($sql, TAB_PRODUCT_CATEGORIES, $productId, $categories);
		if (!empty($categories)) {
			$data = array();
			foreach ($categories as $count=>$catId) {
				$data[] = array(
					'prc_category_id' => $catId,
					'prc_product_id' => $productId,
					'prc_is_default' => (0 == $count) ? 1 : 0
				);
			}
			return self::$DB->insertBatch(TAB_PRODUCT_CATEGORIES, $data, true);
		}
		return true;
	}

	public static function deleteProductThemeDataRecursively($productId, $themes){
		$sql = "SELECT `id`, `ph_path`, `ph_name`, `ph_resized` FROM ?f
				INNER JOIN ?f ON `pt_photo_id` = `id`
				WHERE pt_product_id = ? AND NOT pt_theme_id IN (?)";
		$result = self::$DB->q($sql, TAB_PRODUCT_THEME_PHOTOS, TAB_PHOTOS, $productId, $themes);
		$photo_ids = array();
		$photos = array();
		while($photo = $result->fetch_object()) {
			$photo_ids[] = $photo->id;
			$photos[] = $photo;
		}
		self::$DB->q("DELETE FROM ?f WHERE `id` IN (?)", TAB_PHOTOS, $photo_ids);
		return $photos;
	}

	public static function getProductPhotos($productId){
		$sql = "SELECT `pt_theme_id`, `te_name`, `ph_name`, `ph_path`, `ph_resized` FROM ?f
				LEFT JOIN ?f ON `pt_photo_id`=`photos`.`id`
				LEFT JOIN ?f ON `themes`.`id`=`pt_theme_id` AND `themes`.`deleted` = 0
				WHERE `pt_product_id` = ?";
		return self::find($sql, TAB_PRODUCT_THEME_PHOTOS, TAB_PHOTOS, TAB_THEMES, $productId);
	}

	/**
	 * @param $productId
	 * @param $themeId
	 * @return mixed
	 */

	public static function getProductPhotoForTheme($productId, $themeId){
		$sql = "SELECT `pt_photo_id`, `ph_name`, `ph_path`, `ph_resized` FROM ?f
				LEFT JOIN ?f ON `pt_photo_id`=`id`
				WHERE `pt_product_id` = ? AND `pt_theme_id`=? LIMIT 1";
		$result = self::find($sql, TAB_PRODUCT_THEME_PHOTOS, TAB_PHOTOS, $productId, $themeId);
		return $result ? array_pop($result) : false;
	}

	public static function getAllProducts($limit, $searchSql = '', $orderBy = '') {
		if (empty($searchSql)) {
			$searchSql = '1=1';
			$joinSearch = '';
		} else {
			$joinSearch = " LEFT JOIN " . TAB_TRANSLATIONS . " tr ON tr.t_module = p.p_module AND tr.t_key = p.p_name AND tr.t_lang_id = " . conf('COUNTRY_LANG_ID');
			$joinSearch .= " LEFT JOIN " . TAB_TRANSLATIONS . " tr2 ON tr2.t_module = ct.ct_module AND tr2.t_key = ct.ct_name AND tr2.t_lang_id = " . conf('COUNTRY_LANG_ID');
			$joinSearch .= " LEFT JOIN " . TAB_TRANSLATIONS . " tr3 ON tr3.t_module = te.te_module AND tr3.t_key = te.te_name AND tr3.t_lang_id = " . conf('COUNTRY_LANG_ID');
			$joinSearch .= " LEFT JOIN " . TAB_TRANSLATIONS . " tr4 ON tr4.t_module = s.s_module AND tr3.t_key = s.s_name AND tr3.t_lang_id = " . conf('COUNTRY_LANG_ID');

			$searchSql .= " OR (`tr`.`t_content` LIKE " . self::$DB->escapeValue('%' . str_replace('%', "\\%", post('sSearch')) . '%') . ")";
			$searchSql .= " OR (`tr2`.`t_content` LIKE " . self::$DB->escapeValue('%' . str_replace('%', "\\%", post('sSearch')) . '%') . ")";
			$searchSql .= " OR (`tr3`.`t_content` LIKE " . self::$DB->escapeValue('%' . str_replace('%', "\\%", post('sSearch')) . '%') . ")";
			$searchSql .= " OR (`tr4`.`t_content` LIKE " . self::$DB->escapeValue('%' . str_replace('%', "\\%", post('sSearch')) . '%') . ")";
		}

		if (empty($orderBy)) {
			$orderBy = ' ORDER BY `p`.`id` ASC ';
		}
		$sql = "SELECT `p`.*,`p`.`id` as `product_id`, `pt`.`pt_is_default`, `te`.`te_name`, `s`.`s_name`, `ct`.`ct_name` FROM ?f p
				LEFT JOIN ?f AS `s` ON `p`.`p_size_id` = `s`.`id` AND `s`.`deleted` = 0
				LEFT JOIN ?f AS `ct` ON `ct`.`id` = `p`.`p_cover_type_id` AND `ct`.`deleted` = 0
				LEFT JOIN ?f AS `pt` ON `pt`.`pt_product_id` = `p`.`id` AND `pt`.`pt_is_default` = 1
				LEFT JOIN ?f AS `te` ON `te`.`id` = `pt`.`pt_theme_id` AND `te`.`deleted` = 0
				{$joinSearch}
				WHERE `p`.`deleted` = 0 AND {$searchSql} GROUP BY `product_id` {$orderBy} {$limit}";

		return self::$DB->select($sql, self::$tableName, TAB_SIZES, TAB_COVER_TYPES, TAB_PRODUCT_THEME_PHOTOS, TAB_THEMES );
	}

	/**
	 * Will count all products matching the condition
	 * @param string $sqlCondition A MySQL condition, i.e. `foo` LIKE "bar" AND `snafu` > 0, defaults to 1=1, that is, the function will count all entries
	 * @return int
	 */

	public static function getTotalProducts($sqlCondition = '1=1') {
		if (empty($sqlCondition)) {
			$sqlCondition = '1=1';
		}

		/* we need to join since the sqlCondition filters in joined tables' fields, too */
		$sql = "SELECT COUNT(DISTINCT `p`.`id`) FROM ?f p
				LEFT JOIN ?f AS `s` ON `p`.`p_size_id` = `s`.`id` AND `s`.`deleted` = 0
				LEFT JOIN ?f AS `ct` ON `ct`.`id` = `p`.`p_cover_type_id` AND `ct`.`deleted` = 0
				INNER JOIN ?f AS `pt` ON `pt`.`pt_product_id` = `p`.`id` AND `pt`.`pt_is_default` = 1
				INNER JOIN ?f AS `te` ON `te`.`id` = `pt`.`pt_theme_id` AND `te`.`deleted` = 0
				WHERE `p`.`deleted` = 0 AND {$sqlCondition}";
		return self::$DB->selectCell($sql, self::$tableName, TAB_SIZES, TAB_COVER_TYPES, TAB_PRODUCT_THEME_PHOTOS, TAB_THEMES );
	}

	/**
	 * @static
	 * @param $productId
	 * @return array|bool
	 * this function is used for flash
	 */
	public static function getProductDataByProductId($productId) {
		if(!$productId) {
			return false;
		}

		$sql = "SELECT p.*, p.id as product_id,t.*,t.id as template_id,pp.*,pp.id as page_id,li.*,li.id as li_id,ppt.*,ppi.*,s.*,pt.pt_theme_id FROM ?f p
				INNER JOIN ?f s ON p.p_size_id = s.id
				INNER JOIN ?f pt ON p.id = pt.pt_product_id AND pt.pt_is_default = 1
				LEFT JOIN ?f pp ON p.id = pp.pp_product_id
				LEFT JOIN ?f t ON pp.pp_template_id = t.id
				LEFT JOIN ?f li ON t.id = li.li_owner_id AND li.li_owned_by = 'template'
				LEFT JOIN ?f ppt ON li.id = ppt.ppt_layout_item_id AND pp.id = ppt.ppt_product_page_id
				LEFT JOIN ?f ppi ON li.id = ppi.ppi_layout_item_id AND pp.id = ppi.ppi_product_page_id
				WHERE p.deleted = 0  AND p.id = ?d ORDER BY pp.pp_page_index ASC
				";

		$result  = self::$DB->q($sql, self::$tableName, TAB_SIZES, TAB_PRODUCT_THEME_PHOTOS, TAB_PRODUCT_PAGES, TAB_TEMPLATES, TAB_LAYOUT_ITEMS, TAB_PRODUCT_PAGE_TEXTS, TAB_PRODUCT_PAGE_IMAGES, $productId);
		$dataReturn = array();
		while ($data = $result->fetch_object()) {
			if($data->page_id && $data->li_id) {
				$dataReturn[$data->product_id]['p_photo_gallery_id'] = $data->p_photo_gallery_id;
				$dataReturn[$data->product_id]['s_size_id'] = $data->p_size_id;
				$dataReturn[$data->product_id]['s_face_number'] = $data->s_face_number;
				$dataReturn[$data->product_id]['s_width'] = $data->s_width;
				$dataReturn[$data->product_id]['s_height'] = $data->s_height;
				$dataReturn[$data->product_id]['s_name'] = $data->s_name;
				$dataReturn[$data->product_id]['s_orientation'] = $data->s_orientation;
				$dataReturn[$data->product_id]['p_type'] = $data->p_type;
				$dataReturn[$data->product_id]['p_cover_type_id'] = $data->p_cover_type_id;
				$dataReturn[$data->product_id]['p_name'] = $data->p_name;
				$dataReturn[$data->product_id]['pages'][$data->page_id]['pp_template_id'] = $data->pp_template_id;
				$dataReturn[$data->product_id]['pages'][$data->page_id]['pp_page_index'] = $data->pp_page_index;
				$dataReturn[$data->product_id]['pages'][$data->page_id]['items'][$data->li_id] = $data;
				$dataReturn[$data->product_id]['p_theme_is_default'] = $data->pt_theme_id;
				$dataReturn[$data->product_id]['p_fonts'] = $data->p_fonts;

			} else {
				$dataReturn[$data->product_id]['p_photo_gallery_id'] = $data->p_photo_gallery_id;
				$dataReturn[$data->product_id]['s_size_id'] = $data->p_size_id;
				$dataReturn[$data->product_id]['s_face_number'] = $data->s_face_number;
				$dataReturn[$data->product_id]['s_width'] = $data->s_width;
				$dataReturn[$data->product_id]['s_height'] = $data->s_height;
				$dataReturn[$data->product_id]['s_name'] = $data->s_name;
				$dataReturn[$data->product_id]['s_orientation'] = $data->s_orientation;
				$dataReturn[$data->product_id]['p_type'] = $data->p_type;
				$dataReturn[$data->product_id]['p_cover_type_id'] = $data->p_cover_type_id;
				$dataReturn[$data->product_id]['p_name'] = $data->p_name;
				$dataReturn[$data->product_id]['p_theme_is_default'] = $data->pt_theme_id;
				$dataReturn[$data->product_id]['p_fonts'] = $data->p_fonts;
			}
		}
		return $dataReturn;
	}

	public static function getProductTags($productId) {
		$productTagsArray = array();
		if (!$productId) {
			return $productTagsArray;
		}

		$sql = "SELECT `prt`.`prt_tag_id` FROM ?f prt WHERE `prt`.`prt_product_id` = {$productId}";
		$productTags = self::$DB->select($sql, TAB_PRODUCT_TAGS);

		if ($productTags) {
			foreach ($productTags as $productTag) {
				$productTagsArray[] = $productTag['prt_tag_id'];
			}
		}
		return $productTagsArray;
	}

	public static function rewriteProductTags($productId, $tags) {
		$success = true;
		$data = array();
		if (!$productId) {
			return false;
		}

		$sql = "DELETE FROM ?f WHERE `prt_product_id` = {$productId} ";
		$success = $success && self::$DB->q($sql, TAB_PRODUCT_TAGS);

		if ($tags) {
			foreach ($tags as $tag) {
				$data[] = array(
					'prt_product_id' => $productId,
					'prt_tag_id' => $tag
				);
			}
		}
		self::$DB->insertBatch(TAB_PRODUCT_TAGS, $data, true);
		return $success;
	}
	
	/**
	 * @static
	 * @param $pageId
	 * @return bool|mixed
	 * this function return nr of products by category of page
	 */
	public static function getNrOfProductsByPage($pageId) {
		if (!$pageId) {
			return false;
		}
		$sql = "SELECT COUNT(pd.id) FROM ?f pd
				INNER JOIN ?f prc ON pd.id = prc.prc_product_id
				INNER JOIN ?f cp ON prc.prc_category_id = cp.cp_category_id
				INNER JOIN ?f pr ON cp.cp_project_id = pr.id AND pr.deleted = 0
				INNER JOIN ?f m ON pr.pr_identifier = m.m_project_identifier AND m.deleted = 0
				INNER JOIN ?f mp ON m.id = mp.mp_menu_id AND mp.deleted = 0
				INNER JOIN ?f p ON mp.mp_page_id = p.id AND prc.prc_category_id = p.p_pagination_category_id AND p.p_pagination_type = ? AND p.deleted = 0
				WHERE p.id = ?d GROUP BY pd.id
		";
		$products =  self::$DB->select($sql, self::$tableName, TAB_PRODUCT_CATEGORIES, TAB_CATEGORY_PROJECTS, TAB_PROJECTS, TAB_MENUS, TAB_MENU_PAGES, TAB_PAGES, 'category', $pageId);
		return count($products);
	}

	/**
	 * @static
	 * @param $productId
	 * @param $themeId
	 * @return bool
	 */
	public static function updateDefaultTheme($productId, $themeId) {
		if (!$productId || !$themeId) {
			return false;
		}
		self::$DB->q("UPDATE ?f SET pt_is_default = 0 WHERE pt_product_id = ?d", TAB_PRODUCT_THEME_PHOTOS, $productId);
		self::$DB->q("UPDATE ?f SET pt_is_default = 1 WHERE pt_product_id = ?d AND pt_theme_id = ?d", TAB_PRODUCT_THEME_PHOTOS, $productId, $themeId);
		return true;
	}


	/**
	 * @static
	 * @return array
	 */
	public static function getProductsCategories() {
		$sql = "SELECT p.id as product_id,pc.pc_name FROM ?f p
				INNER JOIN ?f prc ON p.id = prc.prc_product_id
				INNER JOIN ?f pc ON prc.prc_category_id = pc.id AND pc.deleted = 0
				WHERE p.deleted = 0
		";
		$result = self::$DB->q($sql, self::$tableName, TAB_PRODUCT_CATEGORIES, TAB_CATEGORIES);
		$dataReturn = array();
		while ($data = $result->fetch_object()) {
			$dataReturn[$data->product_id][] = $data->pc_name;
		}
		return $dataReturn;
	}

	public static function getFreeEnvelopeProducts($idArray = 0) {
		$sql = "SELECT `p`.`id`, `p`.`p_name` FROM ?f p
		        WHERE `p`.`p_type` = 'envelope' AND  `p`.`deleted` = 0  AND  NOT `id` IN (?)";
		return self::$DB->select($sql, self::$tableName, $idArray);
	}

	public static function getProductsCategoryByCategoryId($categoryId) {
		if (!$categoryId) {
			return false;
		}
		$sql = "SELECT `p`.`id` as `product_id`, `p`.`p_name` FROM ?f prc
				INNER JOIN ?f p ON `p`.`id` = `prc`.`prc_product_id`
				WHERE `prc`.`prc_category_id` = ? ";
		return self::$DB->select($sql, TAB_PRODUCT_CATEGORIES, self::$tableName, $categoryId);
	}

	/**
	 * @static
	 * @param $productId
	 * @param int $themeId
	 * @return bool|mixed
	 */
	public static function getProductDataForPersonalizationByProductId($productId, $themeId = 0) {
		if (!$productId) {
			return false;
		}

		if ($themeId) {
			$sqlAdd = " AND pt_theme_id = " . self::$DB->escapeValue($themeId);
		} else {
			$sqlAdd = " AND pt.pt_is_default = 1";
		}
		$sql = "SELECT p.p_size_id, p.p_name, prc.prc_category_id,ph.ph_resized, ph.ph_path FROM ?f p
				INNER JOIN ?f prc ON p.id = prc.prc_product_id
				INNER JOIN ?f cp ON prc.prc_category_id = cp.cp_category_id AND cp.cp_project_id = ?d
				INNER JOIN ?f pt ON p.id = pt.pt_product_id {$sqlAdd}
				INNER JOIN ?f ph ON pt.pt_photo_id = ph.id AND ph.deleted = 0
				WHERE p.id = ?d AND p.deleted = 0 GROUP BY p.id
		";
		return self::$DB->selectRow($sql, self::$tableName, TAB_PRODUCT_CATEGORIES, TAB_CATEGORY_PROJECTS, self::$PROJECT['id'], TAB_PRODUCT_THEME_PHOTOS, TAB_PHOTOS, $productId);
	}

	public static function getProductsWithSameStylesForStep($sessionMemoryProductsList) {
		if ($sessionMemoryProductsList) {
			$sessionProductsId = array();
			$sessionCategoriesId = array();
			$productStyleId = 0;
			$i=0;
			foreach ($sessionMemoryProductsList as $sessionProduct) {
				if (!isset($sessionProduct['productId']) || !isset($sessionProduct['categoryId'])) {
        			return false;
				}
				$productId = Utils::memoryProductIdDecode($sessionProduct['productId']);
				$sessionProductsId[] = $productId;
				$categoryId = $sessionProduct['categoryId'];
				$sessionCategoriesId[] = $categoryId;

				$product = Db_Products_Products::findArrayById($productId);
				if ($i == 0) {
					$productStyleId = $product['p_group_style'];
				} else {
					if ($productStyleId !=  $product['p_group_style']) {
						return false;
					}
				}
				$i++;
			}

			$sessionProductsId = array_unique($sessionProductsId);
			$sessionCategoriesId = array_unique($sessionCategoriesId);

			$needCategoriesId = Db_Products_CategoryOffers::getConnectedCategoryIdByParentCategoryId($sessionCategoriesId);
			$needCategoriesId = array_unique($needCategoriesId);
			$returnData = array();
			if ($needCategoriesId && $sessionCategoriesId && $sessionProductsId) {
				$sql = "SELECT `pr`.*, `cat`.`pc_type_enum` , `cat`.`pc_name`, `cat`.`id` as `cat_id`, `ph`.`ph_resized`, `ph`.`ph_path` FROM ?f cp
						LEFT JOIN ?f cat ON `cat`.`id` = `cp`.`cp_category_id`
						LEFT JOIN ?f prc ON `prc`.`prc_category_id` = `cp`.`cp_category_id`
						INNER JOIN ?f pr ON `pr`.`id` = `prc`.`prc_product_id`
							AND `pr`.`deleted` = 0
							AND NOT `pr`.`id` IN ( ? )
							AND `p_group_style` = ?
						LEFT JOIN ?f pt ON `pt`.`pt_product_id` = `pr`.`id` AND `pt`.`pt_is_default` = 1
						LEFT JOIN ?f ph ON `ph`.`id` = `pt`.`pt_photo_id`
						WHERE `cp`.`cp_project_id` = ? AND `cp`.`cp_category_id` IN ( ? ) GROUP BY `cp`.`cp_category_id` ";
				$result = self::$DB->select($sql, TAB_CATEGORY_PROJECTS, TAB_CATEGORIES, TAB_PRODUCT_CATEGORIES, TAB_PRODUCTS, $sessionProductsId, $product['p_group_style'], TAB_PRODUCT_THEME_PHOTOS, TAB_PHOTOS, self::$PROJECT['id'], $needCategoriesId);
                if (!$result) {
					return false;
				}
				foreach ($needCategoriesId as $needCategoryId) {
					foreach ($result as $productsReturn) {
						if ($productsReturn['cat_id'] == $needCategoryId) {
							$returnData[] = $productsReturn;
						}
					}
				}
			}
			return $returnData;
		} else {
			return false;
		}
	}

    /**
     * @static
     * @param $cartShop(array from session)
     * @return bool|array
     */
    public static function getProductListForCart($cartShop) {
        $memoryProductListId = array();
        if ($cartShop) {
			foreach ($cartShop as $memoryProductId=>$memoryProductList) {
                $memoryProductListId[] = Utils::memoryProductIdDecode($memoryProductId);
            }
            $sql = "SELECT `cat`.`pc_name_plural`, `cat`.`pc_name`, `mlp`.`id`, `pr`.`p_default_url`, `pr`.`p_name`, `ph1`.`ph_resized` AS `ph1_resized`, `ph1`.`ph_path` AS `ph1_path`, `ph2`.`ph_resized` AS `ph2_resized`, `ph2`.`ph_path` AS `ph2_path`  FROM ?f mlp
                    INNER JOIN ?f pr ON `pr`.`id` = `mlp`.`mlp_product_id` AND `pr`.`deleted` = 0
                    INNER JOIN ?f prc ON `prc`.`prc_product_id` = `pr`.`id`
                    INNER JOIN ?f cat ON `cat`.`id` = `prc`.`prc_category_id` AND `cat`.`deleted` = 0
                    INNER JOIN ?f cp ON `cp`.`cp_category_id` = `cat`.`id` AND `cp`.`cp_project_id` = ?d
                    LEFT JOIN ?f AS ph1 ON `mlp`.`mlp_photo_id` = `ph1`.`id` AND `ph1`.`deleted` = 0
                    INNER JOIN ?f pt ON `pt`.`pt_product_id` = `pr`.`id` AND `pt`.`pt_is_default` = 1
                    LEFT JOIN ?f AS ph2 ON `ph2`.`id` = `pt`.`pt_photo_id` AND `ph2`.`deleted` = 0
                    WHERE `mlp`.`id` IN (?) AND `mlp`.`deleted` = 0
             ";
            $productPhotoAmount = self::$DB->select($sql, TAB_MEMORY_LIST_PRODUCTS, TAB_PRODUCTS, TAB_PRODUCT_CATEGORIES, TAB_CATEGORIES,  TAB_CATEGORY_PROJECTS, self::$PROJECT['id'], TAB_PHOTOS, TAB_PRODUCT_THEME_PHOTOS, TAB_PHOTOS, $memoryProductListId);
        } else {
            return false;
        }
        return $productPhotoAmount;
    }

	/**
	 * @static
	 * @param $tagId
	 * @param null $projectId
	 * @return mixed
	 */
	public static function countProductsByTagId($tagId, $projectId = null) {
		$sqlWhere = null;
		if(!empty($projectId)) {
			$sqlWhere = ' AND `pr`.`id` = ' . $projectId;
		}
		$sql = "SELECT COUNT(DISTINCT `p`.`id`) AS `count_products_by_tag` FROM ?f AS `p`
				INNER JOIN ?f AS `prt` ON `p`.`id` = `prt`.`prt_product_id`
				INNER JOIN ?f AS `tg` ON `prt`.`prt_tag_id` = `tg`.`id`
				INNER JOIN ?f AS `prc` ON `prc`.`prc_product_id` = `p`.`id`
				INNER JOIN ?f AS `ct` ON `ct`.`id` = `prc`.`prc_category_id`
				INNER JOIN ?f AS `cp` ON `cp`.`cp_category_id` = `ct`.`id`
				INNER JOIN ?f AS `pr` ON `pr`.`id` = `cp`.`cp_project_id`
				INNER JOIN ?f AS `s` ON `s`.`id` = `p_size_id`
				INNER JOIN ?f AS `pt` ON `pt`.`pt_product_id` = `p`.`id`
				INNER JOIN ?f AS `te` ON `te`.`id` = `pt`.`pt_theme_id`
				INNER JOIn ?f AS `ph` ON `ph`.`id` = `pt`.`pt_photo_id`
				WHERE `p`.`deleted` = 0 AND `tg`.`deleted` = 0 AND `ct`.`deleted` = 0 AND `pr`.`deleted` = 0
				AND `s`.`deleted` = 0 AND `te`.`deleted` = 0 AND `ph`.`deleted` = 0
				AND `prt`.`prt_tag_id` = ? {$sqlWhere}
				GROUP BY `pr`.`id` ORDER BY `count_products_by_tag` DESC LIMIT 1 ;";
		return self::$DB->selectCell($sql, self::$tableName,
			TAB_PRODUCT_TAGS,
			TAB_TAGS,
			TAB_PRODUCT_CATEGORIES,
			TAB_CATEGORIES,
			TAB_CATEGORY_PROJECTS,
			TAB_PROJECTS,
			TAB_SIZES,
			TAB_PRODUCT_THEME_PHOTOS,
			TAB_THEMES,
			TAB_PHOTOS,
			$tagId);
	}

	public static function getEnvelopesWithSameSize($sessionMemoryProductsList) {
		if (!$sessionMemoryProductsList) {
			return false;
		} else {
			$envelopes = array();
			foreach($sessionMemoryProductsList as $mlpId => $memoryProductsList) {
				if (!$memoryProductsList['productId']) {
					return false;
				}
				$mlpId = Utils::memoryProductIdDecode($mlpId);
				$product = self::findArrayById(Utils::memoryProductIdDecode($memoryProductsList['productId']));
				$sql = "SELECT `pr`.*,
                               `pr`.`id` as `product_id`,
                               `s`.*, `th`.`te_name`,
                               `ph`.*,
                               `col`.`c_color_rgb`,
                               `th`.`id` as `theme_id`,
                               `pt_def`.`pt_theme_id` as `def_theme`,
                               `th_def`.`id` as `def_theme_id`,
                               `th_def`.`te_name` as `def_te_name`,
                               `ph_def`.`ph_path` as `def_ph_path`,
                               `ph_def`.`ph_resized` as `def_ph_resized`
                        FROM ?f pr
				        LEFT JOIN ?f s ON `s`.`id` = `pr`.`p_size_id` AND `s`.`deleted` = 0
				        LEFT JOIN ?f pt ON `pt`.`pt_product_id` = `pr`.`id`
			            LEFT JOIN ?f pt_def ON `pt_def`.`pt_product_id` = `pr`.`id` AND `pt_def`.`pt_is_default` = 1
                        LEFT JOIN ?f ph_def ON `ph_def`.`id` = `pt_def`.`pt_photo_id`
                        LEFT JOIN ?f th_def ON `th_def`.`id` = `pt_def`.`pt_theme_id`
                		LEFT JOIN ?f ph ON `ph`.`id` = `pt`.`pt_photo_id`
                		LEFT JOIN ?f th ON `th`.`id` = `pt`.`pt_theme_id`
                		LEFT JOIN ?f col ON `th`.`te_color_id` = `col`.`id`
				        WHERE `pr`.`deleted` = 0 AND `pr`.`p_size_id` = ? AND `pr`.`p_type` = 'envelope' ";
				 $result = self::$DB->q($sql, self::$tableName, TAB_SIZES, TAB_PRODUCT_THEME_PHOTOS, TAB_PRODUCT_THEME_PHOTOS, TAB_PHOTOS, TAB_THEMES, TAB_PHOTOS, TAB_THEMES, TAB_COLORS, $product['p_size_id']);

				while ($data = $result->fetch_assoc()) {
					if (!isset($envelopes[$mlpId]['product'])) {
						$envelopes[$mlpId]['product'] = $data;
						$envelopes[$mlpId]['product']['themes'] = array();
					}
					$envelopes[$mlpId]['product']['themes'][] = array(
						'ph_path' => $data['ph_path'],
						'ph_resized' => $data['ph_resized'],
						'te_name' => $data['te_name'],
						'c_color_rgb' => $data['c_color_rgb'],
						'theme_id' => $data['theme_id']
						);
				}
				$sql = "SELECT `pc`.* FROM ?f cp
					    LEFT JOIN ?f cat ON `cat`.`id` = `cp`.`cp_category_id` AND `cat`.`pc_type_enum` = 'answer'
					    INNER JOIN ?f pc ON `cat`.`id` = `pc`.`pc_category_id`
										 AND `pc`.`deleted` = 0
										 AND `pc`.`pc_project_id` = ?
										 AND `pc`.`pc_size_id` = ?
				    	WHERE `cp`.`cp_project_id` = ? ";
				$envelopes[$mlpId]['price'] = self::$DB->selectRow( $sql, TAB_CATEGORY_PROJECTS, TAB_CATEGORIES, TAB_PRICES, self::$PROJECT['id'],  $product['p_size_id'], self::$PROJECT['id']);
			}
				return $envelopes;
		}
	}

	public static function getProductWithSameStillForStep($sessionMemoryProductsList) {
		if (!$sessionMemoryProductsList) {
			return false;
		} else {
			$memoryProductsIdList = array();
			$result = array();
			foreach($sessionMemoryProductsList as $memoryProductsList) {
				if (!$memoryProductsList['productId']) {
					return false;
				}
				$memoryProductsIdList[] = Utils::memoryProductIdDecode($memoryProductsList['productId']);
			}
			foreach($sessionMemoryProductsList as $mlpId => $memoryProductsList) {
				$mlpId = Utils::memoryProductIdDecode($mlpId);
				$productId = Utils::memoryProductIdDecode($memoryProductsList['productId']);
				if (!isset($memoryProductsList['categoryId'])) {
					return $result;
				}
				$categoryId = $memoryProductsList['categoryId'];
				$persistentCategoryId = Db_Products_CategoryOffers::getPersistentCategoryIdByParentCategoryId($categoryId);

				$product = self::findArrayById($productId);
				if ($product) {
					$sql = "SELECT `pr`.*, `ph`.*,  `cat`.`pc_type_enum` , `cat`.`pc_name` FROM ?f cp
							LEFT JOIN ?f cat ON `cat`.`id` = `cp`.`cp_category_id`
							LEFT JOIN ?f prc ON `prc`.`prc_category_id` = ?
							INNER JOIN ?f pr ON `pr`.`id` = `prc`.`prc_product_id`
											 AND `pr`.`deleted` = 0
											 AND NOT `pr`.`id` = ?
											 AND `p_group_style` = ?
							LEFT JOIN ?f pt ON `pt`.`pt_product_id` = `pr`.`id` AND `pt`.`pt_is_default` = 1
							LEFT JOIN ?f ph ON `ph`.`id` = `pt`.`pt_photo_id`
							WHERE `cp`.`cp_project_id` = ? ";
					$productsAnswer = self::$DB->selectRow($sql, TAB_CATEGORY_PROJECTS, TAB_CATEGORIES, TAB_PRODUCT_CATEGORIES, $persistentCategoryId, TAB_PRODUCTS, $productId, $product['p_group_style'], TAB_PRODUCT_THEME_PHOTOS, TAB_PHOTOS,  self::$PROJECT['id']);
					if (!in_array($productsAnswer['id'], $memoryProductsIdList)) {
						$result[$mlpId] = $productsAnswer;
					}
				}
			}
			return $result;
		}
	}

	/**
	 * @static
	 * @param $productId
	 * @return mixed
	 */
	public static function getProductThemeIsDefault($productId) {
		$sql = "SELECT `te`.`id` FROM ?f AS `p`
				INNER JOIN ?f AS `pt` ON `pt`.`pt_product_id` = `p`.`id` AND `pt`.`pt_is_default` = 1
				INNER JOIN ?f AS `te` ON `te`.`id` = `pt`.`pt_theme_id`
				WHERE `te`.`deleted` = 0 AND `p`.`deleted` = 0 AND `p`.`id` = ? ;";
		return self::$DB->selectCell($sql, self::$tableName, TAB_PRODUCT_THEME_PHOTOS, TAB_THEMES, $productId);
	}

	/**
	 * @static
	 * @param $url
	 * @param int $productId
	 * @return bool
	 */
	public static function checkProductUrlIsUnique($url, $productId = 0) {
		if (!$url) {
			return false;
		}
		if (!$productId) {
			$productId = 0;
		}

		$product = self::$DB->selectCell("SELECT id FROM ?f WHERE p_default_url = ? AND id != ?d AND deleted = 0",self::$tableName, $url, $productId);
		if ($product) {
			return true;
		}

		if (Db_Cms_Pages::findByField('p_url', $url)) {
			return true;
		}
		if (Db_Cms_PagePagination::findByField('pp_page_url', $url)) {
			return true;
		}
		return false;

	}

	/**
	 * Count all the valid products from a specific category assigned to projects that share the same project identifier
	 * @param int $categoryId The id of the category from which to count the products
	 * @param string $projectIdentifier The identifier of the project
	 * @return bool|int False on failure(invalid arguments supplied or a lack of result from the DB) and the number of counted products on success
	 */
	public static function countValidProductsForCategory($categoryId, $projectIdentifier) {
		if ($categoryId && strlen($projectIdentifier)) {
			$sql = "SELECT DISTINCT COUNT(DISTINCT `p`.`id`)
				FROM ?f m
					INNER JOIN ?f mp ON `m`.`id` = `mp`.`mp_menu_id`
					INNER JOIN ?f pg ON `mp`.`mp_page_id` = `pg`.`id` AND `pg`.`p_dynamic_url` = 1 AND `pg`.`p_pagination_type` = ? AND `pg`.`deleted` = 0
					INNER JOIN ?f pp ON `pp`.`pp_page_id` = `pg`.`id` AND `pp`.`deleted` = 0
					INNER JOIN ?f pc ON `pc`.`prc_category_id` = ?d AND `pp`.`pp_pagination_number` = `pc`.`prc_product_id`
					INNER JOIN ?f p ON `p`.`id` = `pc`.`prc_product_id`  AND `p`.`deleted` = 0
					INNER JOIN ?f s ON `p`.`p_size_id` = `s`.`id`
					INNER JOIN ?f ptf ON `ptf`.`pt_product_id` = `p`.`id`
					INNER JOIN ?f t ON `ptf`.`pt_theme_id` = `t`.`id` AND `t`.`deleted` = 0
					INNER JOIN ?f c ON `t`.`te_color_id` = `c`.`id`
					INNER JOIN ?f ph ON `ptf`.`pt_photo_id` = `ph`.`id` AND `ph`.`deleted` = 0
				WHERE `m`.`m_project_identifier` = ?";
		return self::$DB->selectCell($sql, TAB_MENUS, TAB_MENU_PAGES, TAB_PAGES, 'product', TAB_PAGE_PAGINATION,
			TAB_PRODUCT_CATEGORIES, $categoryId, TAB_PRODUCTS, TAB_SIZES, TAB_PRODUCT_THEME_PHOTOS, TAB_THEMES,
			TAB_COLORS, TAB_PHOTOS, $projectIdentifier);
		} else {
			return false;
		}
	}
}

